package jdbcTest2;

// 删除id最大的一条借阅记录borrow_info

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Delete {
    public static void main(String[] args) throws SQLException {
        // 创建数据源
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("Xianmeng");

        // 建立连接
        Connection connection = dataSource.getConnection();

        // 找最大id
        int maxId = 0;
        // 嵌套查询！
        String sql1 = "select * from borrow_info where id= (select max(id) from borrow_info)";
        PreparedStatement statement1 = connection.prepareStatement(sql1);
        ResultSet resultSet = statement1.executeQuery();
        while (resultSet.next()){
            maxId = resultSet.getInt("id");
            System.out.println("maxId="+maxId);

            // 构造SQL + 预处理
            String sql = "delete from borrow_info where id = " +maxId+ "";
            PreparedStatement statement = connection.prepareStatement(sql);
            System.out.println("statement:"+statement);

            // 执行
            int n = statement.executeUpdate();
            System.out.println("影响条数n="+n);

            // 释放
            statement.close();
        }
        statement1.close();
        connection.close();
    }
}
